const express = require("express");
const router = express.Router();
const multer  = require('multer');
const iconv = require('iconv-lite');
const fs = require('fs');
const csv = require('fast-csv');
const Excel = require('exceljs');

const storage = multer.memoryStorage();
const upload = multer({ storage: storage });

const utils = require("../../utils/index.js");
const pools = require("../../utils/pools.js");
const truncateTable = require("../../utils/truncateTable.js");

// router.post("/uploadExcle", upload.single('file'), async (req, res) => {
//   const costNumber=req.body.costNumber;
//   const equipmentCode=req.body.equipmentCode;
//   console.log(11,costNumber);
//   console.log(22,equipmentCode);
//   const suffix=req.file.originalname.split('.').pop();
//   if(suffix=='csv' || suffix=='CSV'){
//     const content = iconv.decode(req.file.buffer, 'gbk');
//     let csvString=content.toString();
//     console.log(235325325235,csvString);
//     csv.parseString(csvString, { skipRows: 2,headers: false }) // 解析字符串，带有标题行
//       .on("data", function(data){
//         console.log(12,data);
//          // 插入数据库操作
//          let d = new Date(data[6]);  
//          let datetime=d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate() + ' ' + d.getHours() + ':' + d.getMinutes(); 
//          const sql = 'INSERT INTO alarmstatistics (cost_number,equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,alarm_userflag,created_time) VALUES (?,?,?,?,?,?,?,?,?)';
//          pools({sql,val:[costNumber, equipmentCode,data[0], data[5],data[1], data[2],data[3],data[4],new Date()],res,req,run:true});
//       })
//       .on("end", function(){
//         console.log("Parsing complete"); // 完成解析后的操作
//         res.send(utils.returnData({ data: {}}));
//       });
//   }else
//   if(suffix=='xlsx' || suffix=='xls' || suffix=='XLSX' || suffix=='XLS'){
//     const workbook = new Excel.Workbook();
//     workbook.xlsx.load(req.file.buffer)
//       .then(() => {
//         workbook.getWorksheet(1).eachRow({ includeEmpty: true },async (row, rowNumber) => {
//           if (rowNumber > 1) {
//             // 假设第一行是标题行，从第二行开始读取数据
//             const rowData = [];
//             row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
//               rowData.push(cell.text);
//             });
//             // 插入数据库操作
//             let d = new Date(rowData[6]);  
//             let datetime=d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate() + ' ' + d.getHours() + ':' + d.getMinutes(); 
//             const sql = 'INSERT INTO alarmstatistics (cost_number,equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,alarm_userflag,created_time) VALUES (?,?,?,?,?,?,?,?,?)';
//             await pools({sql,val:[costNumber,equipmentCode,rowData[0], rowData[5],rowData[1], rowData[2],rowData[3],rowData[4],new Date()],res,req,run:true});
//           }
//         });
//         // 插入数据库操作
//         // const sql = 'INSERT INTO alarmstatistics (equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,created_time) VALUES ?';
//         // await pools({sql,val:[values],res,req,run:false});
//         // res.send(utils.returnData({ data: {}}));
//         res.send(utils.returnData({ data: {}}));
//     });
//   }else{
//     res.send(utils.returnData({code: -1, msg: "目前只支持xls、xlsx、csv格式导入！",req}));
//   }
// });
router.post("/uploadExcle", upload.single('file'), async (req, res) => {
  const costNumber=req.body.costNumber;
  const equipmentCode=req.body.equipmentCode;
  console.log(11,costNumber);
  console.log(22,equipmentCode);
  const suffix=req.file.originalname.split('.').pop();
  if(suffix=='db' || suffix=='DB'){
    const sqlite3 = require('sqlite3').verbose();
    console.log(9999);
    const db = new sqlite3.Database('./uploads/SQL202502.db'); // 
    console.log(3333,db);
    let number=1
    db.serialize(() => {
      console.log(111);
        // 这里可以写SQL语句来操作数据库，例如读取数据等。这里仅作为示例。
        db.each(`SELECT * FROM TB_CellInfo`, [], (err, row) => { // 替换为你的查询语句和表名
            if (err) {
                res.status(500).send("Error executing query");
            } else {
              const sql = 'INSERT INTO production_metadata (cost_number,equipment_code,JIG_TYPE,JIG_ID,JIG_CHANNEL_ID,TRAY_ID,CHANNEL_ID,CELL_ID,CHECK_RESULT,NG_REASON,DATA_PATH,TEST_START_TIME,TEST_COMPLETE_TIME,INS_TIME,END_VOLT,EDN_CAPT,END_ENGY,END_TEMP,END_PRESS)  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
              pools({sql,val:[costNumber, equipmentCode,row.JIG_TYPE, row.JIG_ID,row.JIG_CHANNEL_ID, row.TRAY_ID,row.CHANNEL_ID,row.CELL_ID,row.CHECK_RESULT, row.NG_REASON,row.DATA_PATH, row.TEST_START_TIME,row.TEST_COMPLETE_TIME,row.INS_TIME, row.END_VOLT,row.EDN_CAPT,row.END_ENGY,row.END_TEMP,row.END_PRESS],res,req,run:true});
            }
        });
    });
    res.send(utils.returnData({ data: {}}));
  }else
  if(suffix=='csv' || suffix=='CSV'){
    const content = iconv.decode(req.file.buffer, 'gbk');
    let csvString=content.toString()
    let totalArray=csvString.split('报警排名')[0].split('C级故障率')[1].replaceAll('\r\n','').split(',');
    console.log(22,totalArray.length);
    console.log(33,totalArray);
    const sql = 'INSERT INTO totaldevicedata (cost_number,device_uuid,production, YIELD,total_number_alarms,a_number_alarms,b_number_alarms,c_number_alarms,total_failure_rate,a_failure_rate,b_failure_rate,c_failure_rate,create_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)';
    pools({sql,val:[costNumber,equipmentCode,totalArray[0], totalArray[1], totalArray[2],totalArray[3],totalArray[4],totalArray[5],totalArray[6],totalArray[7],totalArray[8],totalArray[9],new Date()],res,req,run:true});
    let alarmArray=csvString.split('报警内容');
    let alarmData=alarmArray[alarmArray.length-1].replaceAll(',,,','')
    csv.parseString(alarmData, { skipRows: 0,headers: false }) // 解析字符串，带有标题行
      .on("data", function(data){
        console.log(12,data);
         // 插入数据库操作
        //  let d = new Date(data[6]);  
        //  let datetime=d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate() + ' ' + d.getHours() + ':' + d.getMinutes(); 
         const sql = 'INSERT INTO alarmstatistics (cost_number,equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,alarm_userflag,created_time) VALUES (?,?,?,?,?,?,?,?,?)';
         pools({sql,val:[costNumber, equipmentCode,data[1], data[6],data[2], data[3],data[4],data[5],new Date()],res,req,run:true});
      })
      .on("end", function(){
        console.log("Parsing complete"); // 完成解析后的操作
        res.send(utils.returnData({ data: {}}));
      });
  }else
  if(suffix=='xlsx' || suffix=='xls' || suffix=='XLSX' || suffix=='XLS'){
    const workbook = new Excel.Workbook();
    workbook.xlsx.load(req.file.buffer)
      .then(() => {
        let dataRows=0;
        workbook.getWorksheet(1).eachRow({ includeEmpty: true },async (row, rowNumber) => {
          if (rowNumber == 3) {
            
            // 假设第一行是标题行，从第二行开始读取数据
            const rowData = [];
            row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
              rowData.push(cell.text);
              console.log(111,cell.text);
            });
            const sql = 'INSERT INTO totaldevicedata (cost_number,device_uuid,production, YIELD,total_number_alarms,a_number_alarms,b_number_alarms,c_number_alarms,total_failure_rate,a_failure_rate,b_failure_rate,c_failure_rate,create_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)';
            pools({sql,val:[costNumber,equipmentCode,rowData[0], rowData[1], rowData[2],rowData[3],rowData[4],rowData[5],rowData[6],rowData[7],rowData[8],rowData[9],new Date()],res,req,run:true});
          }
          //获取行号
          if (rowNumber > 5 ) {
            row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
                  if(cell.text=='序号'){
                    dataRows=rowNumber;
                    console.log(3333,dataRows);
                  }
            });
          }
        });
        workbook.getWorksheet(1).eachRow({ includeEmpty: true },async (row, rowNumber) => {
          if (rowNumber > dataRows) {
            const rowData = [];
            row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
              rowData.push(cell.text);
              console.log(999,cell.text);
            });
            // // 插入数据库操作
            // let d = new Date(rowData[6]);  
            // let datetime=d.getFullYear() + '-' + (d.getMonth() + 1) + '-' + d.getDate() + ' ' + d.getHours() + ':' + d.getMinutes(); 
            const sql = 'INSERT INTO alarmstatistics (cost_number,equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,alarm_userflag,created_time) VALUES (?,?,?,?,?,?,?,?,?)';
            await pools({sql,val:[costNumber,equipmentCode,rowData[1], rowData[6],rowData[2], rowData[3],rowData[4],rowData[5],new Date()],res,req,run:true});
          }
        });
        res.send(utils.returnData({ data: {}}));
    });
  }else{
    res.send(utils.returnData({code: -1, msg: "目前只支持xls、xlsx、csv格式导入！",req}));
  }
});
  /**
 * @api {post} /addMore 报警批量新增
 * @apiDescription 报警量新增接口
 * @apiName addMore
 * @apiGroup 报警统计相关接口
 * @apiBody {Object[]} alarmList JSON 数组，包含多组{
    "equipmentCode": "测试编码1",
    "fixtureNumber": 0,
    "alarmContent": "测试报警内容",
    "alarmCode": "123456",
    "alarmLevel": "",
    "alarmTime": "2024-12-31"
}数据集合
 * @apiSuccess {number} code 返回200为成功
 * @apiSuccess {string} message  响应状态
 * @apiSuccessExample {json} 成功回调:
 *  {
 *      code : 200,
 *      message  : "执行成功"
 *  }
 * @apiSampleRequest http://10.3.2.124:3001/alarm/addMore
 * @apiVersion 1.0.0
 */
  router.post("/addMore", async (req, res) => {
    const  obj=req.body.alarmList;
    const values = [];
    const sql = `INSERT INTO  alarmstatistics (equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,created_time) VALUES ?`;
    for(let i=0; i<obj.length; i++) {
      obj[i].createdTime=new Date()
      values[i] = Object.values(obj[i]);
    }
    console.log(666,obj);
    await pools({sql,val:[values],res,req,run:false});
  });
    /**
 * @api {post} /addMoreTemp 报警批量新增(每次提交会覆盖)
 * @apiDescription 报警量新增接口
 * @apiName addMoreTemp
 * @apiGroup 报警统计相关接口
 * @apiBody {Object[]} alarmList JSON 数组，包含多组{
    "equipmentCode": "测试编码1",
    "fixtureNumber": 0,
    "alarmContent": "测试报警内容",
    "alarmCode": "123456",
    "alarmLevel": "",
    "alarmTime": "2024-12-31"
}数据集合
 * @apiSuccess {number} code 返回200为成功
 * @apiSuccess {string} message  响应状态
 * @apiSuccessExample {json} 成功回调:
 *  {
 *      code : 200,
 *      message  : "执行成功"
 *  }
 * @apiSampleRequest http://10.3.2.124:3001/alarm/addMoreTemp
 * @apiVersion 1.0.0
 */
router.post("/addMoreTemp", async (req, res) => {
  const  obj=req.body.alarmList;
  const values = [];
  truncateTable('alarmstatistics');
  const sql = `INSERT INTO  alarmstatistics (equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,created_time) VALUES ?`;
  for(let i=0; i<obj.length; i++) {
    obj[i].createdTime=new Date()
    values[i] = Object.values(obj[i]);
  }
  console.log(666,obj);
  await pools({sql,val:[values],res,req,run:false});
});
  
  /**
 * @api {post} /add 报警新增
 * @apiDescription 报警新增接口
 * @apiName add
 * @apiGroup 报警统计相关接口
 * @apiBody  {String} equipmentCode 设备编码
 * @apiBody  {number} fixtureNumber 治具号
 * @apiBody  {String} alarmContent 报警内容
 * @apiBody  {String} alarmCode 报警代码
 * @apiBody  {String} alarmLevel 报警等级
 * @apiBody  {String} alarmTime 发生时间
 * @apiSuccess {number} code 返回200为成功
 * @apiSuccess {string} message  响应状态
 * @apiSuccessExample {json} 成功回调:
 *  {
 *      code : 200,
 *      message  : "执行成功"
 *  }
 * @apiSampleRequest http://10.3.2.124:3001/alarm/add
 * @apiVersion 1.0.0
 */
  router.post("/add", async (req, res) => {
    let obj=req.body;
    console.log(666,obj);
    let sql = `INSERT INTO  alarmstatistics (equipment_code, fixture_number,alarm_content,alarm_code,alarm_level,alarm_time,created_time) VALUES (?, ?, ?, ?, ?, ?,?)`;
    await pools({sql,val:[obj.equipmentCode,obj.fixtureNumber,obj.alarmContent,obj.alarmCode,obj.alarmLevel,obj.alarmTime,new Date()],res,req,run:false});
    res.json({
      code : 200,
      message  : "成功"
    });
  });

  /**
 * @api {get} /getAll 报警查询
 * @apiDescription 报警查询接口
 * @apiName  getAll
 * @apiGroup 报警统计相关接口
 * @apiBody  {number} page  页面
 * @apiBody  {number} pageSize 每页条数
 * @apiSuccess {number} code 返回200为成功
 * @apiSuccess {string} message  响应状态
 * @apiSuccess {object} data 查询的数据
 * @apiSuccessExample {json} 成功回调:
 *  {
 *      code : 200,
 *      message  : "执行成功",
 *      data:[
 *      {id:'',equipmentCode:"",fixtureNumber:"",alarmContent:"",alarmCode:"",alarmLevel:"",alarmTime:"",createdTime:""},
 *      {id:'',equipmentCode:"",fixtureNumber:"",alarmContent:"",alarmCode:"",alarmLevel:"",alarmTime:"",createdTime:""}
 *     ] 
 *  }
 * @apiSampleRequest http://10.3.2.124:3001/alarm/getAll
 * @apiVersion 1.0.0
 */
router.post("/getAll", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	id,
	cost_number AS costNumber,
	equipment_code AS equipmentCode,
	JIG_ID AS fixtureNumber,
	ALARM_CONTENT AS alarmContent,
	ALARM_CODE AS alarmCode,
	ALARM_LEVEL AS alarmLevel,
	VALID_TAG AS alarmUserflag,
	ALARM_OCCURED_TIME AS alarmTime,
	INS_TIME AS createdTime
FROM
	alarm_metadata   `;
    let {total}=await utils.getSum({sql,name:"alarm_metadata",res,req});
    sql+=` ORDER BY INS_TIME DESC`;
    sql=utils.pageSize(sql,obj.page,obj.pageSize);
    let {result}=await pools({sql,val:[],res,req});
    res.send(utils.returnData({data: result,total}));
});
router.post("/getAllByDevice", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	id,
	cost_number AS costNumber,
	equipment_code AS equipmentCode,
	JIG_ID AS fixtureNumber,
	ALARM_CONTENT AS alarmContent,
	ALARM_CODE AS alarmCode,
	ALARM_LEVEL AS alarmLevel,
	VALID_TAG AS alarmUserflag,
	ALARM_OCCURED_TIME AS alarmTime,
	INS_TIME AS createdTime
FROM
	alarm_metadata  where  equipment_code= '${obj.equipmentCode}'`;
    let {total}=await utils.getSumBySql({sql,res,req});
    sql+=` ORDER BY INS_TIME DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,val:[],res,req});
    res.send(utils.returnData({data: result,total}));
});


router.post("/getAllByCostNumber", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	id,
	cost_number AS costNumber,
	equipment_code AS equipmentCode,
	JIG_ID AS fixtureNumber,
	ALARM_CONTENT AS alarmContent,
	ALARM_CODE AS alarmCode,
	ALARM_LEVEL AS alarmLevel,
	VALID_TAG AS alarmUserflag,
	ALARM_OCCURED_TIME AS alarmTime,
	INS_TIME AS createdTime
FROM
	alarm_metadata  where  cost_number= '${obj.costNumber}' `;
    let {total}=await utils.getSum({sql,name:"alarm_metadata",res,req});
    sql+=` ORDER BY INS_TIME DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,val:[],res,req});
    res.send(utils.returnData({data: result,total}));
});

//项目报警统计汇总
router.post("/getTotalDataByCostNumber", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT cost_number,ALARM_LEVEL,COUNT(*) as number FROM  alarm_metadata where  cost_number= '${obj.costNumber}' GROUP BY ALARM_LEVEL,cost_number`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});





//获取报警折线年汇总数据
router.post("/getAlarmLineDataForYear", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE year_sequence AS (
    SELECT YEAR(CURDATE()) - 9 AS report_year
    UNION ALL
    SELECT report_year + 1 FROM year_sequence WHERE report_year < YEAR(CURDATE())
)
SELECT 
    y.report_year AS year,
    COUNT(alarm_metadata.id) AS number
FROM year_sequence y
LEFT JOIN alarm_metadata 
    ON YEAR(ALARM_OCCURED_TIME) = y.report_year
    AND cost_number = '${obj.costNumber}' 
GROUP BY y.report_year
ORDER BY y.report_year;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取报警折线月汇总数据
router.post("/getAlarmLineDataForMonth", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT 
  CONCAT(YEAR(CURDATE()),'年',months.m,'月')
     AS month,
    COUNT(alarm_metadata.id) AS number
FROM (
    SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) months
LEFT JOIN alarm_metadata 
    ON MONTH(ALARM_OCCURED_TIME) = months.m 
    AND YEAR(ALARM_OCCURED_TIME) = YEAR(CURDATE())
    AND cost_number = '${obj.costNumber}' 
GROUP BY months.m
ORDER BY months.m;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取报警折线周汇总数据
router.post("/getAlarmLineDataForWeek", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE date_sequence AS (
    SELECT 
        MAKEDATE(YEAR(CURDATE()), 1) AS dt,
        WEEK(MAKEDATE(YEAR(CURDATE()), 1), 3) AS iso_week
    UNION ALL
    SELECT 
        dt + INTERVAL 1 DAY,
        WEEK(dt + INTERVAL 1 DAY, 3)
    FROM date_sequence 
    WHERE dt + INTERVAL 1 DAY <= MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL 1 YEAR - INTERVAL 1 DAY
)
SELECT
CONCAT(YEAR(CURDATE()),'年第',iso_week,'周')
     AS week,
    CONCAT(
        DATE_FORMAT(MIN(dt), '%m-%d'),
        ' ~ ',
        DATE_FORMAT(MAX(dt), '%m-%d')
    ) AS weekday,
    COUNT(alarm_metadata.id) AS number
FROM date_sequence
LEFT JOIN alarm_metadata 
    ON DATE(ALARM_OCCURED_TIME) = dt
    AND cost_number = '${obj.costNumber}' 
GROUP BY iso_week
ORDER BY iso_week;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取报警折线日汇总数据
router.post("/getAlarmLineDataForDay", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE date_sequence AS (
    SELECT MAKEDATE(YEAR(CURDATE()), 1) AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_sequence 
    WHERE dt + INTERVAL 1 DAY <= MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL 1 YEAR - INTERVAL 1 DAY
)
SELECT 
    dt AS day,
    COUNT(alarm_metadata.id) AS number
FROM date_sequence
LEFT JOIN alarm_metadata 
    ON DATE(ALARM_OCCURED_TIME) = dt
    AND cost_number = '${obj.costNumber}' 
GROUP BY dt
ORDER BY dt;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});




//获取产量折线年汇总数据
router.post("/getProductLineDataForYear", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE year_sequence AS (
    SELECT YEAR(CURDATE()) - 9 AS report_year
    UNION ALL
    SELECT report_year + 1 FROM year_sequence WHERE report_year < YEAR(CURDATE())
)
SELECT 
    y.report_year AS year,
    COUNT(production_metadata.id) AS number
FROM year_sequence y
LEFT JOIN production_metadata 
    ON YEAR(TEST_COMPLETE_TIME) = y.report_year
    AND cost_number = '${obj.costNumber}' 
GROUP BY y.report_year
ORDER BY y.report_year;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取产量折线月汇总数据
router.post("/getProductLineDataForMonth", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT 
  CONCAT(YEAR(CURDATE()),'年',months.m,'月')
     AS month,
    COUNT(production_metadata.id) AS number
FROM (
    SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) months
LEFT JOIN production_metadata 
    ON MONTH(TEST_COMPLETE_TIME) = months.m 
    AND YEAR(TEST_COMPLETE_TIME) = YEAR(CURDATE())
    AND cost_number = '${obj.costNumber}' 
GROUP BY months.m
ORDER BY months.m;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取产量折线周汇总数据
router.post("/getProductLineDataForWeek", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE date_sequence AS (
    SELECT 
        MAKEDATE(YEAR(CURDATE()), 1) AS dt,
        WEEK(MAKEDATE(YEAR(CURDATE()), 1), 3) AS iso_week
    UNION ALL
    SELECT 
        dt + INTERVAL 1 DAY,
        WEEK(dt + INTERVAL 1 DAY, 3)
    FROM date_sequence 
    WHERE dt + INTERVAL 1 DAY <= MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL 1 YEAR - INTERVAL 1 DAY
)
SELECT 
CONCAT(YEAR(CURDATE()),'年第',iso_week,'周')
     AS week,
    CONCAT(
        DATE_FORMAT(MIN(dt), '%m-%d'),
        ' ~ ',
        DATE_FORMAT(MAX(dt), '%m-%d')
    ) AS weekday,
    COUNT(production_metadata.id) AS number
FROM date_sequence
LEFT JOIN production_metadata 
    ON DATE(TEST_COMPLETE_TIME) = dt
    AND cost_number = '${obj.costNumber}' 
GROUP BY iso_week
ORDER BY iso_week;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});
//获取产量折线日汇总数据
router.post("/getProductLineDataForDay", async (req, res) => {
  let obj=req.body;
  let sql = `WITH RECURSIVE date_sequence AS (
    SELECT MAKEDATE(YEAR(CURDATE()), 1) AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_sequence 
    WHERE dt + INTERVAL 1 DAY <= MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL 1 YEAR - INTERVAL 1 DAY
)
SELECT 
    dt AS day,
    COUNT(production_metadata.id) AS number
FROM date_sequence
LEFT JOIN production_metadata 
    ON DATE(TEST_COMPLETE_TIME) = dt
    AND cost_number = '${obj.costNumber}' 
GROUP BY dt
ORDER BY dt;`;
  let {result}=await pools({sql,val:[],res,req});
  res.send(utils.returnData({data: result}));
});

module.exports = router;
